/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package control;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import model.Driver;

/**
 *
 * @author hans
 */
public class DriverHandler {

    private DatabaseConnection db;

    public DriverHandler(DatabaseConnection db) {
        this.db = db;
    }

    public ArrayList<String[]> getDrivers() throws SQLException {
        ArrayList<String[]> list = new ArrayList<>();

        String sql = "SELECT \n"
                + "	f_name,l_name, \n"
                + "	(SELECT \n"
                + "		group_concat(cert_name separator ', ') \n"
                + "		FROM certificate, certificate_type \n"
                + "		WHERE driver_id = driver.id \n"
                + "		AND cert_id = certificate_type.id\n"
                + "	) as cert_name\n"
                + "	FROM driver;";
        ResultSet rs = db.selectQuery(sql);
        while (rs.next()) {
            String[] str = {rs.getString(1), rs.getString(2), rs.getString(3)};
            list.add(str);
        }

        return list;
    }

    public ArrayList<Driver> getValidDrivers(String date, boolean dg, boolean special, boolean eu) throws SQLException {
        ArrayList<Driver> list = new ArrayList<>();

        String sql = "select * from driver where id not in (select driver_id from shipment where shipment_date = '" + date + "')";
        if (dg) {
            sql = sql + " and id in (select driver_id from certificate where cert_id = 1)";
        }
        if (special) {
            sql = sql + " and id in (select driver_id from certificate where cert_id = 2)";
        }
        if (eu) {
            sql = sql + " and id in (select driver_id from certificate where cert_id = 3)";
        }
        ResultSet rs = db.selectQuery(sql);
        while (rs.next()) {
            list.add(new Driver(rs.getInt("id"), rs.getString("f_name"), rs.getString("l_name")));
        }
        return list;
    }

}
